package com.realtyManage.springboot.Dao;


import com.realtyManage.springboot.Model.Do.User;
import com.realtyManage.springboot.Model.Vo.UserEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

/**
 * 用户服务数据接口类
 *
 * @author 杭州素色网络科技有限公司
 * @since 2019-07-21
 */

@Repository
public interface UserRepository extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User> {
    /*User findByAccount(String account);

    List<User> findByIdGreaterThan(Long id);

    @Query("SELECT U FROM User U ,RoleUser RU WHERE U.id = RU.userId AND RU.roleId = :roleId")
    List<User> findUsersByRole(@Param("roleId") Long roleId);*/

    List<User> findAll();

    User findByTelephone(String phone);

    User findByTelephoneAndPwd(String phone, String pwd);

    List<User> findByTelephoneOrInviteCode(String inviteCode, String inviteCode1);

    List<User> findByLevelAndStatus(int level, int status);

    User findByConnShopIdAndLevelAndStatus(int connShopId, int level, int state);

    @Query("select id from User where name like %?1%")
    List<String> findByNameLike(String upName);

    @Query("select count(id) from User where up_level = ?1 and join_time between ?2 and ?3")
    int countByUpIdAndJoinTimeBetween(Integer uid, Date startTime, Date endTime);

    @Query(value = "select * FROM user WHERE id = ?1 AND level =?2 AND status = ?3", nativeQuery = true)
    List<User> findOneAll(String userId,int condition1,int status);

    @Query(value = "select count(1) FROM customer WHERE conn_user_id = ?1 AND status =?2", nativeQuery = true)
    int countCustomerStatus(String userId, String status);

    @Query(value = "select count(1) FROM user WHERE up_id = ?1", nativeQuery = true)
    int countUserUpId(String userId);

    @Modifying
    @Query(value = "UPDATE house_detail SET periphery =?1 WHERE id = ?2",nativeQuery=true)
    int setPeriphery(String cd,String id);

    @Query(value = "select name from user where  id = ?1",nativeQuery =true)
    String findByName(Integer userId);

    @Query(value = "select id from user where  name = ?1",nativeQuery =true)
    String findId(String name);

    @Query(value = "select sex from user where  id = ?1",nativeQuery =true)
    String findBySex(Integer userId);

    @Query(value = "select name from house_detail where  id = ?1",nativeQuery =true)
    String findByHouseName(Integer houseId);

    @Query(value = "select * from user where  level = ?1 and status = 1",nativeQuery =true)
    List<User> findLevelUserList(String level);

    @Query(value = "select * from user where  level < ?1 and status = 1",nativeQuery =true)
    List<User> findLevelUserListE(String level);

    @Modifying
    @Transactional
    @Query("update User a set " +
            "a.pwd =  CASE WHEN :#{#user.pwd} IS NULL THEN a.pwd ELSE :#{#user.pwd} END " +
            "where a.telephone = :#{#user.phone}")
    int updatePwd(@Param("user") UserEntity user);

    @Query(value = "select reg_time from user where  id = ?1",nativeQuery =true)
    String findRegTime(String uid);

    @Query(value = "select * from user where status = 1 AND `level` < 99 AND `level` > 1",nativeQuery =true)
    List<User> distributionJurisdictionList();

    @Query(value = "select * from user where status = 1 and level =2",nativeQuery =true)
    List<User> queryUserNum();

    @Query(value = "select * from user where id in (?1)",nativeQuery =true)
    List<User> queryUserByIds(List uid);

    @Query(value = "select * from user where id not in (?1) and status = 1 and level =2",nativeQuery =true)
    List<User> queryNUserByIds(List uid);

    @Query(value = "SELECT id,`level`,`name`,jurisdiction,web_show,status FROM `user` WHERE `status` = 1 AND `level` IN(?1)",nativeQuery =true)
    List<String> getRoleUser(List ids);

    @Query(value = "SELECT jurisdiction FROM `user` WHERE `status` = 1 AND id IN(?1)",nativeQuery =true)
    List<String> getUserByJurisdiction(List ids);

    @Modifying
    @Transactional
    @Query("update User a set " +
            "a.integral =  CASE WHEN :#{#u.integral} IS NULL THEN a.integral ELSE :#{#u.integral} END ," +
            "a.jurisdiction =  CASE WHEN :#{#u.jurisdiction} IS NULL THEN a.jurisdiction ELSE :#{#u.jurisdiction} END " +
            "where a.id = :#{#u.id}")
    int updateUserJurisdiction(@Param("u") User u);

    @Query(value = "select jurisdiction from user where  id = ?1",nativeQuery =true)
    String findQx(String userId);

    @Query(value = "SELECT `name`,telephone FROM `user` WHERE `status` = 1 AND `level` = 2",nativeQuery =true)
    List getNameAndPhone();

    @Query(value = "select jurisdiction from user where  id = ?1",nativeQuery =true)
    String findUserQx(Integer userId);

    @Query("select count(id) from User where telephone = ?1 AND status = 1")
    int userCount(String phone);

    @Query(value = "select id from `user` where NAME = ?1 AND status = 1 LIMIT 1",nativeQuery = true)
    int queryUserId(String userName);

    @Query(value = "select id from `user` where phone = ?1 AND status = 1 LIMIT 1",nativeQuery = true)
    int queryUser(String phone);

    @Query(value = "select integral from `user` where id = ?1 AND status = 1 LIMIT 1",nativeQuery = true)
    int getIntegral(Integer id);

    @Modifying
    @Transactional
    @Query("update User a set " +
            "a.name =  CASE WHEN :#{#user.name} IS NULL THEN a.name ELSE :#{#user.name} END, " +
            "a.detail =  CASE WHEN :#{#user.detail} IS NULL THEN a.detail ELSE :#{#user.detail} END, " +
            "a.webShow =  CASE WHEN :#{#user.webShow} IS NULL THEN a.webShow ELSE :#{#user.webShow} END, " +
            "a.level =  CASE WHEN :#{#user.level} IS NULL THEN a.level ELSE :#{#user.level} END, " +
            "a.webShow =  CASE WHEN :#{#user.webShow} IS NULL THEN a.webShow ELSE :#{#user.webShow} END, " +
            "a.staff =  CASE WHEN :#{#user.staff} IS NULL THEN a.staff ELSE :#{#user.staff} END, " +
            "a.status =  CASE WHEN :#{#user.status} IS NULL THEN a.status ELSE :#{#user.status} END, " +
            "a.wechatNumber =  CASE WHEN :#{#user.wechatNumber} IS NULL THEN a.wechatNumber ELSE :#{#user.wechatNumber} END, " +
            "a.projectAddress =  CASE WHEN :#{#user.projectAddress} IS NULL THEN a.projectAddress ELSE :#{#user.projectAddress} END, " +
            "a.img =  CASE WHEN :#{#user.img} IS NULL THEN a.img ELSE :#{#user.img} END " +
            "where a.id = :#{#user.id}")
    int updateMyDetail(@Param("user") User user);

    @Query(value = "SELECT * FROM `user` WHERE `status` = 1 AND `level` in (2,4) and web_show = 1",nativeQuery =true)
    List<User> getReceptionList();

    @Modifying
    @Transactional
    @Query("update User a set " +
            "a.name =  CASE WHEN :#{#user.name} IS NULL THEN a.name ELSE :#{#user.name} END, " +
            "a.detail =  CASE WHEN :#{#user.detail} IS NULL THEN a.detail ELSE :#{#user.detail} END, " +
            "a.webShow =  CASE WHEN :#{#user.webShow} IS NULL THEN a.webShow ELSE :#{#user.webShow} END, " +
            "a.level =  CASE WHEN :#{#user.level} IS NULL THEN a.level ELSE :#{#user.level} END, " +
            "a.jurisdiction =  CASE WHEN :#{#user.jurisdiction} IS NULL THEN a.jurisdiction ELSE :#{#user.jurisdiction} END, " +
            "a.webShow =  CASE WHEN :#{#user.webShow} IS NULL THEN a.webShow ELSE :#{#user.webShow} END, " +
            "a.img =  CASE WHEN :#{#user.img} IS NULL THEN a.img ELSE :#{#user.img} END " +
            "where a.telephone = :#{#user.telephone}")
    int updateUser(@Param("user") User user);


    @Query(value = "select count(id) from `user` where telephone = ?1 AND status = 1 LIMIT 1",nativeQuery = true)
    int verificationUser(String userName);


    @Query(value = "SELECT * FROM `user` WHERE `status` = 1 AND `level` IN (3,4)",nativeQuery =true)
    List<User> findNotice();

    @Query(value = "SELECT * FROM `user` WHERE `status` = 1 AND `level` = 1",nativeQuery =true)
    List<User> findUser();

    @Query(value = "SELECT * FROM `user` a WHERE a.`status` = 1 AND a.`level` IN (2,3,4,7,99) AND a.`name` IS NOT NULL ORDER BY level",nativeQuery = true)
    List<User> getUsrList();

    @Query(value = "SELECT name FROM `user` a WHERE a.`status` = 1 and a.id = ?1",nativeQuery = true)
    String queryUserName(String userId);

    @Query(value = "SELECT name FROM `user` a WHERE a.`status` = 1 and a.telephone = ?1",nativeQuery = true)
    String queryPhone(String userId);

    @Query(value = "SELECT `name`,telephone FROM `user` a WHERE a.`status` = 1 AND staff = 1 AND `name` IS NOT NULL",nativeQuery = true)
    List<String> queryStaff();

    @Query(value = "SELECT * FROM `user` a WHERE a.`status` = 1 AND telephone = ?1 LIMIT 1",nativeQuery = true)
    User findLoginName(String phone);
}
